package com.cqut.forum.dao.imp;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.springframework.stereotype.Component;

import com.cqut.forum.dao.ArticleDao;
import com.cqut.forum.entity.Article;
import com.cqut.forum.entity.User;

@Component
public class ArticleDaoImp extends BaseDaoImp implements ArticleDao{
	
	public <T> boolean unDelete(int id) {
		return false;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getCareUnread(int userId,int moduleId, int index,
			int size) {
		/*String sql = "select f.user_id,f.user_photo,f.user_name,a.article_id,a.article_contents,a.publish_time,a.last_reply_time " +
				"from (select uu.user_id,user_photo,user_name from user uu,relation r where " +
				"r.relation_level=\'好友\' and r.is_care=true and r.user_id = ? and r.friend_id = uu.user_id) as f,article a " +
				"where a.owner_id = f.user_id and a.module_id = ? order by a.last_reply_time desc";
		*/
		
		String sql = "select \'false\' is_picture,f.user_id,f.user_photo,f.user_name,a.article_id target_id,a.article_contents contents," +
					 " a.publish_time create_time,a.last_reply_time update_time " +
					 " from (select distinct uu.user_id,user_photo,user_name from user uu,relation r where " +
					 " r.relation_level=\'好友\' and r.is_care=true and r.user_id = ? and r.friend_id = uu.user_id) as f,article a " +
					 " where a.owner_id = f.user_id and a.module_id =? and a.article_state=true"+ 
					 " UNION "+
					 " (select \'true\' is_picture,f.user_id,f.user_photo,f.user_name,p.picture_id target_id,p.picture_address contents," +
					 " p.upload_time create_time,p.last_review_time update_time " +
					 " from (select distinct uu.user_id,user_photo,user_name from user uu,relation r where " +
					 " r.relation_level=\'好友\' and r.is_care=true and r.user_id = ? and r.friend_id = uu.user_id) as f,picture p " +
					 " where p.owner_id = f.user_id and p.picture_state=true) order by update_time desc";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(moduleId);
		parameters.add(userId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);
		
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getUserInfo(int userId, int moduleId,
			int index, int size) {
		String sql1 = "select f.user_id,f.user_photo,f.user_name,a.article_id,a.article_contents,a.publish_time,a.last_reply_time " +
					  "from (select distinct uu.user_id,user_photo,user_name from user uu,relation r where " +
					  //"(uu.user_id=?) or (r.relation_level=\'好友\' and r.user_id = ? and r.friend_id = uu.user_id)) as f,article a " +
					  "uu.user_id=?) as f,article a " +
					  "where a.owner_id = f.user_id and a.article_state=true and a.module_id =? order by a.last_reply_time desc";
		
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		//parameters.add(userId);
		parameters.add(moduleId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql1, parameters, pages);
		
		return result;
	}
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getAllUnread(int userId,int moduleId, int index, int size) {
		String sql = "select \'false\' is_picture,f.user_id,f.user_photo,f.user_name,a.article_id target_id,a.article_contents contents," +
					 " a.publish_time create_time,a.last_reply_time update_time " +
					 " from (select distinct uu.user_id,user_photo,user_name from user uu,relation r where " +
					 " (uu.user_id=?) or (r.relation_level=\'好友\' and r.user_id = ? and r.friend_id = uu.user_id)) as f,article a " +
					 " where a.owner_id = f.user_id and a.module_id =? and a.article_state=true"+ 
					 " UNION "+
					 " (select \'true\' is_picture,f.user_id,f.user_photo,f.user_name,p.picture_id target_id,p.picture_address contents," +
					 " p.upload_time create_time,p.last_review_time update_time " +
					 " from (select distinct uu.user_id,user_photo,user_name from user uu,relation r where " +
					 " (r.relation_level=\'好友\' and r.user_id = ? and r.friend_id = uu.user_id) or (uu.user_id = ?)) as f,picture p " +
					 " where p.owner_id = f.user_id and p.picture_state=true) order by update_time desc";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(userId);
		parameters.add(moduleId);
		parameters.add(userId);
		parameters.add(userId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);
		
		return result;
	}
	
	/*@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getAllUnread(int userId,int moduleId, int index, int size) {
		(select picture_id,upload_time,picture_address from picture where owner_id=2) 
		 * union 
		 *(select article_id,publish_time,article_contents from article where owner_id=1) 
		 *order by upload_time desc limit 1,20; 
		String sql1 = "select f.user_id,f.user_photo,f.user_name,a.article_id,a.article_contents,a.publish_time,a.last_reply_time " +
				"from (select distinct uu.user_id,user_photo,user_name from user uu,relation r where " +
				"(uu.user_id=?) or (r.relation_level=\'好友\' and r.user_id = ? and r.friend_id = uu.user_id)) as f,article a " +
				"where a.owner_id = f.user_id and a.module_id =? order by a.last_reply_time desc";
		
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(userId);
		parameters.add(moduleId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql1, parameters, pages);
		
		return result;
	}*/

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> getById(int articleId) {
		String sql = "select a.article_id,a.article_contents,a.publish_time,u.user_photo,u.user_name from article a,user u " +
				"where a.article_id = ? and a.owner_id = u.user_id";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(articleId);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, null);

		return result.size()>0?result.get(0):null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> get(int index, int size, int moduleId) {
		/*String sql = "select ag.article_group_name,a.article_name,a.article_id,a.visit_times,a.review_times,a.publish_time,u.user_photo," +
				"u.user_name,u.user_id " +
				"from article a,user u,article_group ag " +
				"where a.module_id = ? and a.owner_id = u.user_id and ag.article_group_id = a.article_group_id order by a.publish_time desc";
		*/String sql1 = "select a.article_name,a.article_id,a.visit_times,a.review_times,a.publish_time,u.user_photo," +
				"u.user_name,u.user_id from article a,user u where a.module_id=? and a.owner_id=u.user_id " +
				"and a.article_state=true and a.is_public=true order by a.publish_time desc";
		
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(moduleId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql1, parameters, pages);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getHotArticle(int index, int size) {
		String sql = "select a.article_name,a.article_key_words,a.article_id,a.review_times,u.user_photo,u.user_name " +
				"from article a,user u where a.owner_id = u.user_id and a.module_id = 1 " +
				"and a.article_state=true and a.is_public=true order by a.review_times desc";
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, null, pages);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getByOwnerId(int moduleId,int userId,int index,int size) {
		String hql = "select * from article a where a.owner_id=? and a.module_id=? and a.article_state=true";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(moduleId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(hql, parameters, pages);
		
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getByOwnerId(int moduleId, String keyWords,
			int userId, int index, int size) {
		String hql = "select * from article a where a.owner_id=? and a.module_id=? and a.article_state=true and " +
				"(a.article_name like \"%"+keyWords+"%\" or a.article_key_words like \"%"+keyWords+"%\")";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(moduleId);
		//parameters.add(keyWords);
		//parameters.add(keyWords);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(hql, parameters, pages);
		
		return result;
	}
	@SuppressWarnings("unchecked")
	@Override
	public String getArticleGroupName(int articleId) {
		String hql = "select a.articleGroup.articleGroupName from Article a where a.articleId=?";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(articleId);
		
		List<String> result = (List<String>) super.excuteHql(hql, parameters, null);

		return result.size()>0?result.get(0):null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getArticleVisitor(int articleId) {
		String sql = "select u.user_id,u.user_photo,u.user_name,ar.article_reply_time " +
				"from user u,article a,article_reply ar where u.user_id=ar.user_id and " +
				"a.article_id=ar.article_id and a.article_id=? " +
				"order by ar.article_reply_time desc";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(articleId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", 0);
		pages.put("size", 7);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);

		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<User> getHotArticleUser(int index, int size) {
		String hql = "select distinct  u from User u,Article a where u.userId=a.ownerId " +
				"and u.userState=true order by a.reviewTimes desc";
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<User> result = (List<User>)super.excuteHql(hql, null, pages);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Article> getByGroupId(int moduleId, int groupId, int index,
			int size) {
		String hql = "select a from Article a where a.moduleId=? and " +
				"a.articleState=true and a.articleGroup.articleGroupId=?";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(moduleId);
		parameters.add(groupId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Article> result = (List<Article>)super.excuteHql(hql, parameters, pages);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getArticleByCase(int userId,int moduleId,int index,int size,String option) {
		String sql = "";
		if(moduleId==3){
			sql = "select a.*,ag.article_password from article a,article_group ag where a.owner_id=? and a.module_id = ? " +
				"and a.article_state=? and a.article_group_id=ag.article_group_id ";
		}
		else{
			sql = "select a.* from article a where a.owner_id=? and a.module_id = ? " +
					"and a.article_state=? ";
		}
		sql = sql + option;
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(moduleId);
		parameters.add(true);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>)super.excuteSql(sql, parameters, pages);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getModuleVisitors(int userId,
			int moduleId, int index, int size) {
		/*Map<String, Object> map = new HashMap<String, Object>();
			map.put("userId", m.get("user_id"));
			map.put("userPhoto", m.get("user_photo"));
			map.put("userName", m.get("user_name"));
			map.put("visitDate", ((Date)m.get("article_reply_time")).toLocaleString());
			result.add(map);*/
		String sql = "select u.user_id,u.user_photo,u.user_sex,u.user_name,un.news_time from user u,user_news un " +
					 "where un.user_id=u.user_id and un.accept_user_id=? and un.module_id=? " +
					 "and un.is_system_news=false order by un.news_time desc";
		
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(moduleId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>)super.excuteSql(sql, parameters, pages);
		return result;
	}

	@Override
	public boolean haveReadMyArticle(int userId, int moduleId) {
		String sql = "update article_reply ar,article a set ar.article_reply_state=false where ar.article_id=a.article_id " +
					 "and a.owner_id=? and a.module_id=?";
		Query query = super.getSqlQuery(sql);
		query.setInteger(0, userId);
		query.setInteger(1, moduleId);
		query.executeUpdate();
		return true;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getArticles(String articleOwner,
			String articleTitle, String publishTime,int moduleId, int index, int size) {
		String sql = "select a.article_id,u.user_name,a.article_name,ag.article_group_name," +
				"a.publish_time,a.article_state,a.article_contents from article a,user u,article_group ag where " +
				"a.article_group_id=ag.article_group_id and a.article_state=true and a.owner_id=u.user_id and a.module_id="+moduleId+" ";
		if(!articleOwner.equals("")){
			sql = sql + "and u.user_name like \"%"+articleOwner+"%\" ";
		}
		if(!articleTitle.equals("")){
			sql = sql + "and a.article_name like \"%"+articleTitle+"%\" ";
		}
		if(null!=publishTime&&!"".equals(publishTime)){
			sql = sql + "and a.publish_time>\""+publishTime+"\"";
		}
		sql = sql + " order by a.publish_time";
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, null, pages);
		return result;
	}

	@Override
	public boolean updateArticleState(int userId,int articleId, boolean state) {
		String sql = "update article a set a.article_state="+state+" where a.article_id="+articleId+
		" and a.owner_id="+userId;
		
		Query query = super.getSqlQuery(sql);
		query.executeUpdate();
		return true;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> loadGardens(int userId,String publishTime,
			String ballKind, String userRelation,int moduleId, int index, int size) {
		String sql = "select a.article_name,a.article_id,a.visit_times,a.review_times,a.publish_time,u.user_photo," +
				"u.user_name,u.user_id,ag.article_password,ag.article_group_name from user u,article a,article_group ag where " +
				"a.owner_id=u.user_id and a.article_state=true and a.article_group_id=ag.article_group_id and a.module_id="+moduleId+" ";
		if(ballKind!=null&&!ballKind.equals("")){
			sql = sql + " and ag.article_group_name like \"%"+ballKind+"%\" ";
		}
		if(publishTime!=null&&!publishTime.equals("")){
			sql = sql + " and a.publish_time>\""+publishTime+"\" ";
		}
		if(userRelation!=null&&!"".equals(userRelation)){
			if(userRelation.equals("myself")){
				sql = sql + " and a.owner_id="+userId;
				//sql = sql + "and ";
			}
			else if(userRelation.equals("friend")){
				sql = sql + " and a.owner_id in (select r.friend_id from relation r where r.user_id="+userId+")";
			}
			else{
				//sql = sql + "";
			}
		}
		sql = sql + " order by a.publish_time";
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", index);
		pages.put("size", size);
		
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, null, pages);
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public int count(int userId, int moduleId) {
		String sql = "select count(a.article_id) from article a where a.owner_id=? and a.module_id=? " +
				"and a.article_state=true";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(userId);
		parameters.add(moduleId);
		
		List<Map<String, BigInteger>> result = (List<Map<String, BigInteger>>) super.excuteSql(sql, parameters, null);
		return (Integer)(result.get(0).get("count(a.article_id)").intValue());
	}

	@SuppressWarnings("unchecked")
	@Override
	public int count(int moduleId) {
		String sql = "select count(a.article_id) from article a where a.module_id=? " +
		"and a.article_state=true";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(moduleId);
		
		List<Map<String, BigInteger>> result = (List<Map<String, BigInteger>>) super.excuteSql(sql, parameters, null);
		return (Integer)(result.get(0).get("count(a.article_id)").intValue());
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> getNext(int articleId) {
		String sql = "select a.article_id,a.article_name from article a,user u where " +
				"a.article_id>? and a.module_id=1 and a.owner_id=(select a.owner_id from article a where " +
				"a.article_id=?) order by a.article_id asc";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(articleId);
		parameters.add(articleId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", 0);
		pages.put("size", 1);
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);
		return result.size()>0?result.get(0):null;
	}
	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> getPrevious(int articleId) {
		String sql = "select a.article_id,a.article_name from article a,user u where " +
		"a.article_id<? and a.module_id=1 and a.owner_id=(select a.owner_id from article a where " +
		"a.article_id=?) order by a.article_id desc";
		List<Object> parameters = new ArrayList<Object>();
		parameters.add(articleId);
		parameters.add(articleId);
		
		Map<String, Integer> pages = new HashMap<String, Integer>();
		pages.put("index", 0);
		pages.put("size", 1);
		List<Map<String, Object>> result = (List<Map<String, Object>>) super.excuteSql(sql, parameters, pages);
		return result.size()>0?result.get(0):null;
	}
}
